use SuperStar
go

/*************************
******** DaiLy ***********/

/* Get list */
if exists (select name from sysobjects where name ='spu_daily_getAll')
	drop proc spu_daily_getAll
go

create proc spu_daily_getAll
as
	select * from DaiLy
go

/* Get by id */
if exists (select name from sysobjects where name ='spu_daily_getById')
	drop proc spu_daily_getById
go

create proc spu_daily_getById(@ID int)
as
	select * from DaiLy where ID=@ID
go

/* Add */
if exists (select name from sysobjects where name ='spu_daily_add')
	drop proc spu_daily_add
go

create proc spu_daily_add(@TenDaiLy nvarchar(100), @DiaChi nvarchar(150), @DienThoai nvarchar(15))
as
	insert into DaiLy (TenDaiLy, DiaChi, DienThoai) values(@TenDaiLy, @DiaChi, @DienThoai)
go

/* Update */
if exists (select name from sysobjects where name ='spu_daily_update')
	drop proc spu_daily_update
go

create proc spu_daily_update(@ID int, @TenDaiLy nvarchar(100), @DiaChi nvarchar(150), @DienThoai nvarchar(15))
as
	update DaiLy set TenDaiLy = @TenDaiLy, DiaChi = @DiaChi, DienThoai = @DienThoai where ID = @ID
go

/* Delete */
if exists (select name from sysobjects where name ='spu_daily_delete')
	drop proc spu_daily_delete
go

create proc spu_daily_delete(@ID int)
as
	delete from DaiLy where ID = @ID
go

/************************************************************************/

/*************************
******** LoaiSanPham ***********/

/* Get list */
if exists (select name from sysobjects where name ='spu_loaisanpham_getAll')
	drop proc spu_loaisanpham_getAll
go

create proc spu_loaisanpham_getAll
as
	select * from loaisanpham
go

/* Get by id */
if exists (select name from sysobjects where name ='spu_loaisanpham_getById')
	drop proc spu_loaisanpham_getById
go

create proc spu_loaisanpham_getById(@ID int)
as
	select * from loaisanpham where ID=@ID
go

/* Add */
if exists (select name from sysobjects where name ='spu_loaisanpham_add')
	drop proc spu_loaisanpham_add
go

create proc spu_loaisanpham_add(@TenLoaiSanPham nvarchar(50))
as
	insert into LoaiSanPham(TenLoaiSanPham) values(@TenLoaiSanPham)
go

/* Update */
if exists (select name from sysobjects where name ='spu_loaisanpham_update')
	drop proc spu_loaisanpham_update
go

create proc spu_loaisanpham_update(@ID int, @TenLoaiSanPham nvarchar(50))
as
	update LoaiSanPham set TenLoaiSanPham = @TenLoaiSanPham where ID = @ID
go

/* Delete */
if exists (select name from sysobjects where name ='spu_loaisanpham_delete')
	drop proc spu_loaisanpham_delete
go

create proc spu_loaisanpham_delete(@ID int)
as
	delete from LoaiSanPham where ID = @ID
go

/************************************************************************/

/*************************
******** NguoiDung ***********/

/* Get list count */
if exists (select name from sysobjects where name ='spu_nguoidung_getCount')
	drop proc spu_nguoidung_getCount
go

create proc spu_nguoidung_getCount
as
	select COUNT(*) from NguoiDung
go

/* Get list */
if exists (select name from sysobjects where name ='spu_nguoidung_getAll')
	drop proc spu_nguoidung_getAll
go

create proc spu_nguoidung_getAll
as
	select * from NguoiDung
go

/* Get by id */
if exists (select name from sysobjects where name ='spu_nguoidung_getById')
	drop proc spu_nguoidung_getById
go

create proc spu_nguoidung_getById(@ID int)
as
	select * from NguoiDung where ID=@ID
go

/* Get by username */
if exists (select name from sysobjects where name ='spu_nguoidung_getByUsername')
	drop proc spu_nguoidung_getByUsername
go

create proc spu_nguoidung_getByUsername(@Username nvarchar(50))
as
	select * from NguoiDung where Username=@Username
go

/* Add */
if exists (select name from sysobjects where name ='spu_nguoidung_add')
	drop proc spu_nguoidung_add
go

create proc spu_nguoidung_add(@UserName nvarchar(50), @Password nvarchar(50), @TenNguoiDung nvarchar(100), @NgaySinh datetime, @Email nvarchar(50), 
	@DiaChi nvarchar(150), @DienThoai nvarchar(50), @IDLoaiNguoiDung int, @DiemThuong int, @IDDaiLy int)
as
	insert into NguoiDung (Username, [Password], TenNguoiDung, NgaySinh, Email, DiaChi, DienThoai, IDLoaiNguoiDung, DiemThuong, IDDaiLy)
		values(@UserName, @Password, @TenNguoiDung, @NgaySinh, @Email, @DiaChi, @DienThoai, @IDLoaiNguoiDung, @DiemThuong, @IDDaiLy)
go

/* Update */
if exists (select name from sysobjects where name ='spu_nguoidung_update')
	drop proc spu_nguoidung_update
go

create proc spu_nguoidung_update(@ID int, @UserName nvarchar(50), @Password nvarchar(50), @TenNguoiDung nvarchar(100), @NgaySinh datetime, @Email nvarchar(50), @DiaChi nvarchar(150), @DienThoai nvarchar(50), @IDLoaiNguoiDung int, @DiemThuong int, @IDDaiLy int)
as
	update NguoiDung set Username = @UserName, [Password] = @Password, TenNguoiDung = @TenNguoiDung, NgaySinh = @NgaySinh, Email = @Email, DiaChi = @DiaChi, DienThoai = @DienThoai, IDLoaiNguoiDung = @IDLoaiNguoiDung, DiemThuong = @DiemThuong, IDDaiLy = @IDDaiLy where ID = @ID
go

/* Delete */
if exists (select name from sysobjects where name ='spu_nguoidung_delete')
	drop proc spu_nguoidung_delete
go

create proc spu_nguoidung_delete(@ID int)
as
	delete from NguoiDung where ID = @ID
go

/* Get by LoaiNguoiDung */
if exists (select name from sysobjects where name ='spu_nguoidung_getByLoaiNguoiDung')
	drop proc spu_nguoidung_getByLoaiNguoiDung
go

create proc spu_nguoidung_getByLoaiNguoiDung(@IDLoaiNguoiDung int)
as
	select * from NguoiDung where IDLoaiNguoiDung=@IDLoaiNguoiDung
go

/* Sign In */
if exists (select name from sysobjects where name ='spu_nguoidung_signIn')
	drop proc spu_nguoidung_signIn
go

create proc spu_nguoidung_signIn(@Username nvarchar(50),@Password nvarchar(50))
as
	select * from NguoiDung where Username=@Username and Password=@Password
go

/************************************************************************/

/*************************
******** NguoiDung_SanPham ***********/

/* Get by username, idTrangThaiSanPham by daily */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByUsernameIdTrangThaiSanPham')
	drop proc spu_nguoidung_sanpham_getByUsernameIdTrangThaiSanPham
go

create proc spu_nguoidung_sanpham_getByUsernameIdTrangThaiSanPham(@Username nvarchar(50), @IdTrangThaiSanPham int,@IsPhanThuong int,@IDDaiLy int)
as
	select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,sp.HinhAnh,ttsp.TenTrangThaiSanPham,dl.TenDaiLy from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp,DaiLy dl,NguoiDung nd where nd.Username like '%'+@Username+'%' and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=@IdTrangThaiSanPham and ndsp.IDTrangThaiSanPham=ttsp.ID and dl.ID=ndsp.IDDaiLyNhanSanPham and ndsp.IsPhanThuong=@IsPhanThuong and dl.ID=@IDDaiLy
go

/* Get by idNguoiDung, idTrangThaiSanPham */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByIdNguoiDungIdTrangThaiSanPham')
	drop proc spu_nguoidung_sanpham_getByIdNguoiDungIdTrangThaiSanPham
go

create proc spu_nguoidung_sanpham_getByIdNguoiDungIdTrangThaiSanPham(@IdNguoiDung int, @IdTrangThaiSanPham int,@IsPhanThuong int)
as
	select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,sp.HinhAnh,ttsp.TenTrangThaiSanPham,dl.TenDaiLy from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp,DaiLy dl where ndsp.IDNguoiDung = @IDNguoiDung and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=@IdTrangThaiSanPham and ndsp.IDTrangThaiSanPham=ttsp.ID and dl.ID=ndsp.IDDaiLyNhanSanPham and ndsp.IsPhanThuong=@IsPhanThuong
go

/* Get by idNguoiDung, idTrangThaiSanPham limit */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByIdNguoiDungIdTrangThaiSanPhamLimit')
	drop proc spu_nguoidung_sanpham_getByIdNguoiDungIdTrangThaiSanPhamLimit
go

create proc spu_nguoidung_sanpham_getByIdNguoiDungIdTrangThaiSanPhamLimit(@IdNguoiDung int, @IdTrangThaiSanPham int, @start int, @end int,@IsPhanThuong int)
as
	with Rows as
	(
		select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,sp.HinhAnh,ttsp.TenTrangThaiSanPham,dl.TenDaiLy,ROW_NUMBER() Over (order by sp.TenSanPham) as Row
		from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp,DaiLy dl
		where ndsp.IDNguoiDung=@IDNguoiDung and @IdTrangThaiSanPham=@IdTrangThaiSanPham and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=ttsp.ID and dl.ID=ndsp.IDDaiLyNhanSanPham and ndsp.IsPhanThuong=@IsPhanThuong
	)
	select top (@end) ID,IDNguoiDung,IDSanPham,SoLuong,DonGia,TongGia,NgayMua,IDTrangThaiSanPham,IDDaiLyNhanSanPham,IsPhanThuong,DiemThuong,TongDiemThuong,TenSanPham,HinhAnh,TenTrangThaiSanPham,TenDaiLy
	from Rows
	where Row>@start
go

/* Get by Username */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByUsername')
	drop proc spu_nguoidung_sanpham_getByUsername
go

create proc spu_nguoidung_sanpham_getByUsername(@Username nvarchar(50))
as
	select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,sp.HinhAnh,ttsp.TenTrangThaiSanPham,dl.TenDaiLy from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp,NguoiDung nd,DaiLy dl where nd.Username=@Username and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=ttsp.ID and dl.ID=ndsp.IDDaiLyNhanSanPham
go

/* Get by idNguoiDung */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByIdNguoiDung')
	drop proc spu_nguoidung_sanpham_getByIdNguoiDung
go

create proc spu_nguoidung_sanpham_getByIdNguoiDung(@IdNguoiDung int,@IsPhanThuong int)
as
	select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,sp.HinhAnh,ttsp.TenTrangThaiSanPham,dl.TenDaiLy,sp.ThoiGianBatDauNhanHang,sp.ThoiGianKetThucNhanHang from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp,DaiLy dl where ndsp.IDNguoiDung = @IDNguoiDung and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=ttsp.ID and ndsp.IDDaiLyNhanSanPham=dl.ID and ndsp.IsPhanThuong=@IsPhanThuong
go

/* Get by idNguoiDungLimit */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByIdNguoiDungLimit')
	drop proc spu_nguoidung_sanpham_getByIdNguoiDungLimit
go

create proc spu_nguoidung_sanpham_getByIdNguoiDungLimit(@IdNguoiDung int, @start int, @end int,@IsPhanThuong int)
as
	with Rows as
	(
		select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,sp.HinhAnh,ttsp.TenTrangThaiSanPham,dl.TenDaiLy,ROW_NUMBER() Over (order by sp.TenSanPham) as Row
		from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp,DaiLy dl
		where ndsp.IDNguoiDung=@IDNguoiDung and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=ttsp.ID and ndsp.IDDaiLyNhanSanPham=dl.ID and ndsp.IsPhanThuong=@IsPhanThuong
	)
	select top (@end) ID,IDNguoiDung,IDSanPham,SoLuong,DonGia,TongGia,NgayMua,IDTrangThaiSanPham,IDDaiLyNhanSanPham,IsPhanThuong,DiemThuong,TongDiemThuong,TenSanPham,HinhAnh,TenTrangThaiSanPham,TenDaiLy
	from Rows
	where Row>@start
go

/* Get by id */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getById')
	drop proc spu_nguoidung_sanpham_getById
go

create proc spu_nguoidung_sanpham_getById(@ID int)
as
	select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,sp.HinhAnh,ttsp.TenTrangThaiSanPham from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp where ndsp.ID=@ID and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=ttsp.ID
go

/* Get by idDaiLyNhanSanPham */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByIdDaiLyNhanSanPham')
	drop proc spu_nguoidung_sanpham_getByIdDaiLyNhanSanPham
go

create proc spu_nguoidung_sanpham_getByIdDaiLyNhanSanPham(@IdDaiLyNhanSanPham int)
as
	select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,ttsp.TenTrangThaiSanPham from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp where ndsp.IDDaiLyNhanSanPham=@IdDaiLyNhanSanPham and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=ttsp.ID
go

/* Get by idDaiLyNhanSanPham, idTrangThaiSanPham */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByIdDaiLyNhanSanPhamIdTrangThaiSanPham')
	drop proc spu_nguoidung_sanpham_getByIdDaiLyNhanSanPhamIdTrangThaiSanPham
go

create proc spu_nguoidung_sanpham_getByIdDaiLyNhanSanPhamIdTrangThaiSanPham(@IdDaiLyNhanSanPham int, @IdTrangThaiSanPham int)
as
	select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,ttsp.TenTrangThaiSanPham from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp where ndsp.IDDaiLyNhanSanPham=@IdDaiLyNhanSanPham and ndsp.IDTrangThaiSanPham=@IdTrangThaiSanPham and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=ttsp.ID
go

/* Get by idDaiLyNhanSanPhamLimit */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByIdDaiLyNhanSanPhamLimit')
	drop proc spu_nguoidung_sanpham_getByIdDaiLyNhanSanPhamLimit
go

create proc spu_nguoidung_sanpham_getByIdDaiLyNhanSanPhamLimit(@IDDaiLyNhanSanPham int, @start int, @end int)
as
	with Rows as
	(
		select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,ttsp.TenTrangThaiSanPham,ROW_NUMBER() Over (order by sp.TenSanPham) as Row
		from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp
		where ndsp.IDDaiLyNhanSanPham=@IDDaiLyNhanSanPham and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=ttsp.ID
	)
	select top (@end) ID,IDNguoiDung,IDSanPham,SoLuong,DonGia,TongGia,NgayMua,IDTrangThaiSanPham,IDDaiLyNhanSanPham,IsPhanThuong,DiemThuong,TongDiemThuong,TenSanPham,TenTrangThaiSanPham
	from Rows
	where Row>@start
go

/* Get by idDaiLyNhanSanPham, @idTrangThaiSanPham limit */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_getByIdDaiLyNhanSanPhamIdTrangThaiSanPhamLimit')
	drop proc spu_nguoidung_sanpham_getByIdDaiLyNhanSanPhamIdTrangThaiSanPhamLimit
go

create proc spu_nguoidung_sanpham_getByIdDaiLyNhanSanPhamIdTrangThaiSanPhamLimit(@IdDaiLyNhanSanPham int, @IdTrangThaiSanPham int, @start int, @end int)
as
	with Rows as
	(
		select ndsp.ID,ndsp.IDNguoiDung,ndsp.IDSanPham,ndsp.SoLuong,ndsp.DonGia,ndsp.TongGia,ndsp.NgayMua,ndsp.IDTrangThaiSanPham,ndsp.IDDaiLyNhanSanPham,ndsp.IsPhanThuong,ndsp.DiemThuong,ndsp.TongDiemThuong,sp.TenSanPham,ttsp.TenTrangThaiSanPham,ROW_NUMBER() Over (order by sp.TenSanPham) as Row
		from NguoiDung_SanPham ndsp,SanPham sp,TrangThaiSanPham ttsp
		where ndsp.IDDaiLyNhanSanPham=@IdDaiLyNhanSanPham and ndsp.IDTrangThaiSanPham=@IdTrangThaiSanPham and ndsp.IDSanPham=sp.ID and ndsp.IDTrangThaiSanPham=ttsp.ID
	)
	select top (@end) ID,IDNguoiDung,IDSanPham,SoLuong,DonGia,TongGia,NgayMua,IDTrangThaiSanPham,IDDaiLyNhanSanPham,IsPhanThuong,DiemThuong,TongDiemThuong,TenSanPham,TenTrangThaiSanPham
	from Rows
	where Row>@start
go

/* Add */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_add')
	drop proc spu_nguoidung_sanpham_add
go

create proc spu_nguoidung_sanpham_add(@IDNguoiDung int, @IDSanPham int, @SoLuong int, @DonGia decimal(18,0), @TongGia decimal(18, 0), @NgayMua datetime, @IDTrangThaiSanPham int, @IDDaiLyNhanSanPham int, @IsPhanThuong tinyint, @DiemThuong int, @TongDiemThuong int)
as
	insert into NguoiDung_SanPham (IDNguoiDung, IDSanPham, SoLuong, DonGia, TongGia, NgayMua, IDTrangThaiSanPham, IDDaiLyNhanSanPham, IsPhanThuong, DiemThuong, TongDiemThuong)
		values(@IDNguoiDung, @IDSanPham, @SoLuong, @DonGia, @TongGia, @NgayMua, @IDTrangThaiSanPham, @IDDaiLyNhanSanPham, @IsPhanThuong, @DiemThuong, @TongDiemThuong)
go

/* Update */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_update')
	drop proc spu_nguoidung_sanpham_update
go

create proc spu_nguoidung_sanpham_update(@ID int, @IDNguoiDung int, @IDSanPham int, @SoLuong int, @DonGia decimal(18,0), @TongGia decimal(18, 0), @NgayMua datetime, @IDTrangThaiSanPham int, @IDDaiLyNhanSanPham int, @IsPhanThuong tinyint, @DiemThuong int, @TongDiemThuong int)
as
	update NguoiDung_SanPham set IDNguoiDung = @IDNguoiDung, IDSanPham = @IDSanPham, SoLuong = @SoLuong, DonGia = @DonGia, TongGia = @TongGia, IDTrangThaiSanPham = @IDTrangThaiSanPham, IDDaiLyNhanSanPham = @IDDaiLyNhanSanPham, IsPhanThuong=@IsPhanThuong, DiemThuong=@DiemThuong, TongDiemThuong=@TongDiemThuong where ID = @ID
go

/* Delete */
if exists (select name from sysobjects where name ='spu_nguoidung_sanpham_delete')
	drop proc spu_nguoidung_sanpham_delete
go

create proc spu_nguoidung_sanpham_delete(@ID int)
as
	delete from NguoiDung_SanPham where ID = @ID
go

/************************************************************************/

/*************************
******** SanPham ***********/

/* Get list count */
if exists (select name from sysobjects where name ='spu_sanpham_getCount')
	drop proc spu_sanpham_getCount
go

create proc spu_sanpham_getCount
as
	select COUNT(*) from sanpham
go

/* Get list count in sale time */
if exists (select name from sysobjects where name ='spu_sanpham_getCountInSaleTime')
	drop proc spu_sanpham_getCountInSaleTime
go

create proc spu_sanpham_getCountInSaleTime
as
	select COUNT(*) from sanpham where GETDATE() >= ThoiGianBatDauBan and GETDATE() <= ThoiGianKetThucBan
go

/* Get list count by LoaiSanPham in sale time */
if exists (select name from sysobjects where name ='spu_sanpham_getCountInSaleTimeByIDLoaiSanPham')
	drop proc spu_sanpham_getCountInSaleTimeByIDLoaiSanPham
go

create proc spu_sanpham_getCountInSaleTimeByIDLoaiSanPham(@IDLoaiSanPham int)
as
	select COUNT(*) from sanpham where IDLoaiSanPham=@IDLoaiSanPham and GETDATE() >= ThoiGianBatDauBan and GETDATE() <= ThoiGianKetThucBan
go

/* Get list limit */
if exists (select name from sysobjects where name ='spu_sanpham_getLimit')
	drop proc spu_sanpham_getLimit
go

create proc spu_sanpham_getLimit(@start int, @end int)
as
	with Rows as
	(
		select *,ROW_NUMBER() Over (order by TenSanPham) as Row
		from SanPham
	)
	select top (@end) ID,TenSanPham,IDLoaiSanPham,Gia,SoLuong,DiemThuong,ThoiGianBatDauBan,ThoiGianKetThucBan,ThoiGianBatDauNhanHang,ThoiGianKetThucNhanHang,DiemNhanThuong,HinhAnh
	from Rows
	where Row>@start
go

/* Get list */
if exists (select name from sysobjects where name ='spu_sanpham_getAll')
	drop proc spu_sanpham_getAll
go

create proc spu_sanpham_getAll
as
	select * from sanpham
go

/* Get by id */
if exists (select name from sysobjects where name ='spu_sanpham_getById')
	drop proc spu_sanpham_getById
go

create proc spu_sanpham_getById(@ID int)
as
	select * from sanpham where ID=@ID
go

/* Get by idLoaisanPham */
if exists (select name from sysobjects where name ='spu_sanpham_getByIdLoaiSanPham')
	drop proc spu_sanpham_getByIdLoaiSanPham
go

create proc spu_sanpham_getByIdLoaiSanPham(@IDLoaiSanPham int)
as
	select * from sanpham where IDLoaiSanPham=@IDLoaiSanPham
go

/* Get by idLoaiSanPhamLimit */
if exists (select name from sysobjects where name ='spu_sanpham_getByIdLoaiSanPhamLimit')
	drop proc spu_sanpham_getByIdLoaiSanPhamLimit
go

create proc spu_sanpham_getByIdLoaiSanPhamLimit(@IDLoaiSanPham int, @start int, @end int)
as
	with Rows as
	(
		select *,ROW_NUMBER() Over (order by TenSanPham) as Row
		from SanPham
		where IDLoaiSanPham=@IDLoaiSanPham
	)
	select top (@end) ID,TenSanPham,IDLoaiSanPham,Gia,SoLuong,DiemThuong,ThoiGianBatDauBan,ThoiGianKetThucBan,ThoiGianBatDauNhanHang,ThoiGianKetThucNhanHang,DiemNhanThuong
	from Rows
	where Row>@start
go

/* Add */
if exists (select name from sysobjects where name ='spu_sanpham_add')
	drop proc spu_sanpham_add
go

create proc spu_sanpham_add(@TenSanPham nvarchar(100), @IDLoaiSanPham int, @Gia decimal(18,0), @SoLuong int, @DiemThuong int, 
	@ThoiGianBatDauBan datetime, @ThoiGianKetThucBan datetime, @ThoiGianBatDauNhanHang datetime, @ThoiGianKetThucNhanHang datetime, @DiemNhanThuong int,@HinhAnh nvarchar(255))
as
	insert into SanPham(TenSanPham, IDLoaiSanPham, Gia, SoLuong, DiemThuong, ThoiGianBatDauBan, ThoiGianKetThucBan, ThoiGianBatDauNhanHang, ThoiGianKetThucNhanHang, DiemNhanThuong,HinhAnh)
		values(@TenSanPham, @IDLoaiSanPham, @Gia, @SoLuong, @DiemThuong, @ThoiGianBatDauBan, @ThoiGianKetThucBan, @ThoiGianBatDauNhanHang, @ThoiGianKetThucNhanHang, @DiemNhanThuong,@HinhAnh)
	select max(ID) from SanPham
go

/* Update */
if exists (select name from sysobjects where name ='spu_sanpham_update')
	drop proc spu_sanpham_update
go

create proc spu_sanpham_update(@ID int, @TenSanPham nvarchar(100), @IDLoaiSanPham int, @Gia decimal(18,0), @SoLuong int, @DiemThuong int, 
	@ThoiGianBatDauBan datetime, @ThoiGianKetThucBan datetime, @ThoiGianBatDauNhanHang datetime, @ThoiGianKetThucNhanHang datetime, @DiemNhanThuong int,@HinhAnh nvarchar(255))
as
	update sanpham set Tensanpham = @Tensanpham, IDLoaiSanPham = @IDLoaiSanPham, Gia = @Gia, SoLuong = @SoLuong, DiemThuong = @DiemThuong,
		ThoiGianBatDauBan = @ThoiGianBatDauBan, ThoiGianKetThucBan = @ThoiGianKetThucBan, ThoiGianBatDauNhanHang = @ThoiGianBatDauNhanHang, 
		ThoiGianKetThucNhanHang = @ThoiGianKetThucNhanHang, DiemNhanThuong = @DiemNhanThuong, HinhAnh=@HinhAnh where ID = @ID
go

/* Delete */
if exists (select name from sysobjects where name ='spu_sanpham_delete')
	drop proc spu_sanpham_delete
go

create proc spu_sanpham_delete(@ID int)
as
	delete from sanpham where ID = @ID
go

/* Get list in sale time */
if exists (select name from sysobjects where name ='spu_sanpham_getListInSaleTime')
	drop proc spu_sanpham_getListInSaleTime
go

create proc spu_sanpham_getListInSaleTime
as
	select * from sanpham where GETDATE() >= ThoiGianBatDauBan and GETDATE() <= ThoiGianKetThucBan
go

/* Get limit list in sale time */
if exists (select name from sysobjects where name ='spu_sanpham_getLimitInSaleTime')
	drop proc spu_sanpham_getLimitInSaleTime
go

create proc spu_sanpham_getLimitInSaleTime(@start int, @end int)
as
	with Rows as
	(
		select *,ROW_NUMBER() Over (order by TenSanPham) as Row
		from SanPham
	)
	select top (@end) ID,TenSanPham,IDLoaiSanPham,Gia,SoLuong,DiemThuong,ThoiGianBatDauBan,ThoiGianKetThucBan,ThoiGianBatDauNhanHang,ThoiGianKetThucNhanHang,DiemNhanThuong,HinhAnh
	from Rows
	where Row>@start and GETDATE() >= ThoiGianBatDauBan and GETDATE() <= ThoiGianKetThucBan
go

/* Get by idLoaiSanPhamLimit in sale time */
if exists (select name from sysobjects where name ='spu_sanpham_getByIdLoaiSanPhamLimitInSaleTime')
	drop proc spu_sanpham_getByIdLoaiSanPhamLimitInSaleTime
go

create proc spu_sanpham_getByIdLoaiSanPhamLimitInSaleTime(@IDLoaiSanPham int, @start int, @end int)
as
	with Rows as
	(
		select *,ROW_NUMBER() Over (order by TenSanPham) as Row
		from SanPham
		where IDLoaiSanPham=@IDLoaiSanPham and GETDATE() >= ThoiGianBatDauBan and GETDATE() <= ThoiGianKetThucBan
	)
	select top (@end) ID,TenSanPham,IDLoaiSanPham,Gia,SoLuong,DiemThuong,ThoiGianBatDauBan,ThoiGianKetThucBan,ThoiGianBatDauNhanHang,ThoiGianKetThucNhanHang,DiemNhanThuong,HinhAnh
	from Rows
	where Row>@start
go

/************************************************************************/

/*************************
******** LoaiNguoiDung ***********/

/* Get list */
if exists (select name from sysobjects where name ='spu_loainguoidung_getAll')
	drop proc spu_loainguoidung_getAll
go

create proc spu_loainguoidung_getAll
as
	select * from LoaiNguoiDung
go

/*************************************************************************/

/*************************
******** TrangThaiSanPham ***********/

/* Get list */
if exists (select name from sysobjects where name ='spu_trangthaisanpham_getAll')
	drop proc spu_trangthaisanpham_getAll
go

create proc spu_trangthaisanpham_getAll
as
	select * from TrangThaiSanPham
go

/*********************************************************************************/

/*************************
******** ThamSo ***********/

/* Get list */
if exists (select name from sysobjects where name ='spu_ThamSo_getAll')
	drop proc spu_ThamSo_getAll
go

create proc spu_ThamSo_getAll
as
	select * from ThamSo
go

/*********************************************************************************/